﻿Imports System.Data.OleDb
Public Class Edit_Sheet
    Dim cmd As OleDbCommand
    Dim dr As OleDbDataReader
    Dim a As AutoCompleteStringCollection
    Dim totaldebit As Double
    Dim totalcredit As Double
    Dim qry As String
    Private Sub Edit_Sheet_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            fillparty()
            tblmain.Enabled = False
            DateTimePicker1.Checked = False
            DateTimePicker1.Value = Today.Date
            fillcmb()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub fillcmb()
        Dim str As String
        cmbparty.Items.Clear()
        str = "select customer_id,customer_name from customer_master order by customer_name"
        cmd = New OleDbCommand(str, con)
        dr = cmd.ExecuteReader
        While dr.Read
            cmbparty.Items.Add(dr(1).ToString)
        End While
    End Sub
    Private Sub fillparty()
        Dim str As String
        a = New AutoCompleteStringCollection
        str = "select customer_name from customer_master order by customer_name"
        cmd = New OleDbCommand(str, con)
        dr = cmd.ExecuteReader
        While dr.Read
            a.Add(dr(0).ToString)
        End While
    End Sub

    Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
        If btnclose.Text = "Cancel" Then
            If MessageBox.Show("Are you sure you want to Cancel this sheet", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
                btnadd.Text = "Edit"
                tblmain.Enabled = False
                btnclose.Text = "Close"
            End If
        ElseIf btnclose.Text = "Close" Then
            Me.Close()
        End If
    End Sub

    Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
        Dim str As String
        Dim n As Long
        If btnadd.Text = "Edit" Then
            btnadd.Text = "Save"
            tblmain.Enabled = True
            btnclose.Text = "Cancel"
            tblmain.Enabled = True
        ElseIf btnadd.Text = "Save" Then
            If dgridladger.Rows.Count = 0 Then
                MsgBox("No Record for updation")
                Exit Sub
            End If
            While dgridladger.Rows.Count > 0
                str = "select customer_id from customer_master where customer_name='" & dgridladger.Item(1, 0).Value & "'"
                cmd = New OleDbCommand(str, con)
                dr = cmd.ExecuteReader
                If dr.Read Then
                    n = Val(dr(0).ToString)
                End If
                str = "update ladger set accountid=" & n & ",narration='" & dgridladger.Item(3, 0).Value & "',damount=" & Val(dgridladger.Item(4, 0).Value) & ",camount=" & Val(dgridladger.Item(5, 0).Value) & ",billdate=#" & dgridladger.Item(2, 0).Value & "# where voucherid=" & Val(dgridladger.Item(0, 0).Value)
                cmd = New OleDbCommand(str, con)
                If cmd.ExecuteNonQuery > 0 Then
                    dgridladger.Rows.RemoveAt(0)
                Else
                    MsgBox("Error while updation", MsgBoxStyle.Critical)
                    Exit Sub
                End If
            End While
            btnclose.Text = "Close"
            btnadd.Text = "Edit"
            tblmain.Enabled = False
            dgridladger.Rows.Clear()
        End If
    End Sub

    Private Sub DateTimePicker1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DateTimePicker1.KeyDown
        Dim str As String
        If e.KeyCode = Keys.Enter Then
            If cmbparty.Text = "" Then
                str = "select voucherID,customer_name,billdate,narration,damount,camount from ladger,customer_master where accountid=customer_id and billdate=#" & Format(DateTimePicker1.Value, "dd/MMM/yyyy") & "#"
                qry = str
            Else
                str = "select voucherID,customer_name,billdate,narration,damount,camount from ladger,customer_master where accountid=customer_id and accountid=(select customer_id from customer_master where customer_name='" & cmbparty.Text & "')   and billdate=#" & Format(DateTimePicker1.Value, "dd/MMM/yyyy") & "# "
                qry = str
            End If
            fillgrid(str)
            caltotal()
        End If
    End Sub

    Private Sub fillgrid(ByVal str As String)
        Dim i As Integer
        dgridladger.Rows.Clear()
        cmd = New OleDbCommand(str, con)
        dr = cmd.ExecuteReader
        While dr.Read
            dgridladger.Rows.Add()
        End While
        cmd = New OleDbCommand(str, con)
        dr = cmd.ExecuteReader
        While dr.Read
            dgridladger.Item(0, i).Value = dr(0).ToString
            dgridladger.Item(1, i).Value = dr(1)
            dgridladger.Item(2, i).Value = Format(dr(2), "dd/MMM/yyyy")
            dgridladger.Item(3, i).Value = dr(3).ToString
            dgridladger.Item(4, i).Value = dr(4).ToString
            dgridladger.Item(5, i).Value = dr(5).ToString
            i = i + 1
        End While
    End Sub
  
    Private Sub dgridladger_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles dgridladger.EditingControlShowing
        If dgridladger.CurrentCell.ColumnIndex = 0 Then
            Dim tb As TextBox = CType(e.Control, TextBox)
            tb.AutoCompleteSource = AutoCompleteSource.CustomSource
            tb.AutoCompleteCustomSource = a
            tb.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        ElseIf dgridladger.CurrentCell.ColumnIndex = 3 Or dgridladger.CurrentCell.ColumnIndex = 4 Then
            Dim tb As TextBox = CType(e.Control, TextBox)
            tb.AutoCompleteSource = AutoCompleteSource.None
            AddHandler tb.KeyPress, AddressOf txtrollno_keypress
            AddHandler tb.KeyUp, AddressOf txtamount_KeyUp
        End If
    End Sub
    Private Sub txtrollno_keypress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        If sender.text.contains(e.KeyChar) = True Then
            If Asc(e.KeyChar) = 46 Then
                e.KeyChar = ""
            End If
        End If
        If IsNumeric(e.KeyChar) = False And Asc(e.KeyChar) <> 8 And Asc(e.KeyChar) <> 46 Then
            e.KeyChar = ""
        End If
    End Sub
    Private Sub caltotal()
        Dim i As Integer
        Dim count As Integer
        count = dgridladger.Rows.Count
        lbltotaldebit.Text = 0
        lblcredittotal.Text = 0
        While i < count
            lbltotaldebit.Text = Val(lbltotaldebit.Text) + Val(dgridladger.Item(3, i).Value)
            lblcredittotal.Text = Val(lblcredittotal.Text) + Val(dgridladger.Item(4, i).Value)
            i = i + 1
        End While
    End Sub
    Private Sub txtamount_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
        If dgridladger.CurrentCell.ColumnIndex = 3 Then
            dgridladger.Item(3, dgridladger.CurrentRow.Index).Value = sender.text
            caltotal()
        ElseIf dgridladger.CurrentCell.ColumnIndex = 4 Then
            dgridladger.Item(4, dgridladger.CurrentRow.Index).Value = sender.text
            caltotal()
        End If
    End Sub
    Private Sub TextBox1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtnarration.KeyDown
        If e.KeyCode = Keys.Enter Then
            Dim str As String
            If cmbparty.Text = "" Then
                MsgBox("No party selected")
                Exit Sub
            End If
            dgridladger.Rows.Clear()
            If DateTimePicker1.Checked = True Then
                str = "select voucherID,customer_name,billdate,narration,damount,camount from ladger,customer_master where accountid=customer_id and accountid=(select customer_id from customer_master where customer_name='" & cmbparty.Text & "')   and billdate=#" & Format(DateTimePicker1.Value, "dd/MMM/yyyy") & "# and narration like '%" & txtnarration.Text & "%'"
                qry = str
            else
                str = "select voucherID,customer_name,billdate,narration,damount,camount from ladger,customer_master where accountid=customer_id and accountid=(select customer_id from customer_master where customer_name='" & cmbparty.Text & "')  and narration like '%" & txtnarration.Text & "%'"
                qry = str
            End If
            fillgrid(str)
            caltotal()
        End If
    End Sub

    Private Sub cmbparty_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles cmbparty.KeyDown
        Dim str As String
        If e.KeyCode = Keys.Enter Then
            str = "select voucherID,customer_name,billdate,narration,damount,camount from ladger,customer_master where accountid=customer_id and accountid=(select customer_id from customer_master where customer_name='" & cmbparty.Text & "')"
            qry = str
            fillgrid(str)
            caltotal()
        End If
    End Sub
    Private Sub dgridladger_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgridladger.KeyDown
        Dim str As String
        If dgridladger.Rows.Count = 0 Then
            MsgBox("No record for deletion")
            Exit Sub
        Else
            If MessageBox.Show("Are you sure you want to delete this record", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
                str = "delete from ladger where voucherid=" & Val(dgridladger.Item(0, dgridladger.CurrentRow.Index).Value)
                cmd = New OleDbCommand(str, con)
                cmd.ExecuteNonQuery()
            Else
                fillgrid(qry)
            End If
        End If
    End Sub
End Class